package snapex.expense.service.impl;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import lombok.extern.slf4j.Slf4j;
import snapex.core.ExpenseStatus;
import snapex.core.Status;
import snapex.core.mapper.ext.ExpenseSqlProvider;
import snapex.core.model.CurrencyRate;
import snapex.core.model.Expense;
import snapex.core.model.ProjectCustomerInfo;
import snapex.core.model.Result;
import snapex.core.model.SearchParams;
import snapex.core.model.StaffInfo;
import snapex.core.model.UserAuthority;
import snapex.core.model.UserGroups;
import snapex.expense.service.IExpenseService;
import snapex.expense.service.ISyncService;

@Slf4j
@Transactional
@Service
public class SyncService implements ISyncService{
	@Autowired
	private HtcExpenseServiceFacade htcExpenseService;
	
	@Autowired
	private IExpenseService expenseService;
		
	@Autowired @Qualifier("snapexJdbcTemplate")
	private JdbcTemplate snapexJdbcTemplate;
	
	@Override
	public Result syncStaffInfo() {		
		
		List<StaffInfo> staffs = htcExpenseService.getAllStaffInfo();
				
		snapexJdbcTemplate.execute("delete from t_staff_info");
		
		snapexJdbcTemplate.batchUpdate("insert into t_staff_info (stf_no,stf_nameeng,stf_teamid,stf_lnaddr) values (?,?,?,?)", staffs, 300, new ParameterizedPreparedStatementSetter<StaffInfo>() {
			@Override
			public void setValues(PreparedStatement ps, StaffInfo staff) throws SQLException {
				ps.setString(1, staff.getStfNo());
				ps.setString(2, staff.getStfNameeng());
				ps.setString(3, staff.getStfTeamid());
				ps.setString(4, staff.getStfLnaddr());
			}
		});
		
		
		return Result.success().msg("Sync staff info success:" + staffs.size());
	}
	
	@Override
	public Result syncProjectCustomerInfo() {
		
		List<ProjectCustomerInfo> projects = htcExpenseService.getProjectCustomerInfo();

		snapexJdbcTemplate.execute("delete from t_project_customer_info");
		
		snapexJdbcTemplate.batchUpdate("insert into t_project_customer_info (pc_gmis_project_id,pc_customer_name,pc_cost_center_mandatory) values (?,?,?)", projects, 300, new ParameterizedPreparedStatementSetter<ProjectCustomerInfo>() {
			@Override
			public void setValues(PreparedStatement ps, ProjectCustomerInfo project) throws SQLException {
				ps.setString(1, project.getPcGmisProjectId());
				ps.setString(2, project.getPcCustomerName());
				ps.setString(3, project.getPcCostCenterMandatory());
			}
		});
		return Result.success().msg("Sync project customer info success:"+ projects.size());
	}
	
	@Override
	public Result syncCurrencyRate(String rateMonth) {		
		List<CurrencyRate> rates = htcExpenseService.getAllCurrencyRate(rateMonth);
		
		snapexJdbcTemplate.execute("delete from t_currency_rate");
		
		snapexJdbcTemplate.batchUpdate("insert into t_currency_rate (currency,rate,rate_month) values (?,?,?)", rates, 300, new ParameterizedPreparedStatementSetter<CurrencyRate>() {
			@Override
			public void setValues(PreparedStatement ps, CurrencyRate rate) throws SQLException {
				ps.setString(1, rate.getCurrency());
				ps.setBigDecimal(2, rate.getRate());
				ps.setString(3, rate.getRateMonth());
			}
		});
		
		return Result.success().msg("Sync currency rate success:" + rates.size());
	}
	
	@Override
	public Result syncUserGroups() {		
		List<UserGroups> groups = htcExpenseService.getUserGroups();

		snapexJdbcTemplate.execute("delete from t_user_groups");
		
		snapexJdbcTemplate.batchUpdate("insert into t_user_groups (ugp_name,ugp_desc,ugp_appid,ugp_type,ugp_cat,ugp_cost_centre,ugp_parent) values (?,?,?,?,?,?,?)",
				groups, 300, new ParameterizedPreparedStatementSetter<UserGroups>() {
			@Override
			public void setValues(PreparedStatement ps, UserGroups group) throws SQLException {
				ps.setString(1, group.getUgpName());
				ps.setString(2, group.getUgpDesc());
				ps.setString(3, group.getUgpAppid());
				ps.setInt(4, group.getUgpType());
				ps.setInt(5, group.getUgpCat());
				ps.setString(6, group.getUgpCostCentre());
				ps.setString(7, group.getUgpParent());
			}
		});		
		return Result.success().msg("Sync user groups success:" + groups.size());
	}
	
	@Override
	public Result syncUserAuthority() {
		List<UserAuthority> authorities = htcExpenseService.getUserAuthority();
		
		snapexJdbcTemplate.execute("delete from t_user_authority");
		
		snapexJdbcTemplate.batchUpdate("insert into t_user_authority (uat_user,uat_type,uat_lastmodtime) values (?,?,?)",
				authorities, 300, new ParameterizedPreparedStatementSetter<UserAuthority>() {
			@Override
			public void setValues(PreparedStatement ps, UserAuthority authority ) throws SQLException {
				ps.setString(1, authority.getUatUser());
				ps.setString(2, authority.getUatType());
				ps.setTimestamp(3, authority.getUatLastmodtime());
			}
		});	
		
		return Result.success().msg("Sync user authority success:" + authorities.size());
	}
	
	@Override
	public Result syncExpenses(SearchParams params) {
		
		params.setSyncTimestamp(new Timestamp(System.currentTimeMillis()));
		
		//set sync_timestamp = date_add(current_timestamp,interval 1 year) where sync_timestamp is null
		snapexJdbcTemplate.execute(ExpenseSqlProvider.markSyncTimetamp());
		
		//where sync_timestamp >= current_timestamp
		List<Expense> expenses = expenseService.exportExpenses(params);
		
		Result result = htcExpenseService.syncExpenses(expenses);
				
		if(result.getCode().equals(Status.Ok.getCode())) {
			//set sync_timestamp = current_timestamp where year(sync_timestamp) > year(current_timestamp)
			snapexJdbcTemplate.execute(ExpenseSqlProvider.updateSyncTimetamp());
		}else {
			//set sync_timestamp = null where year(sync_timestamp) > year(current_timestamp)			
			snapexJdbcTemplate.execute(ExpenseSqlProvider.rollbackSyncTimetamp());
		}
		
		return result;		
	}
}
